{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 音乐网站用户流失预测 -- 数据预处理（数据校正）\n",
    "\n",
    "### 数据集说明\n",
    "\n",
    "项目提供KKBOX用户——歌曲重复播放记录，以及用户和歌曲的元数据。训练数据由2017年2月服务到期的用户构成，target标签代表用户在2017年3月是否续订了业务。测试集中的数据由2017年3月内将到期的用户构成，需要预测用户是否在到期后的一个月内即2017年4月预定、流失的概率。\n",
    "\n",
    "以下是文件及字段说明：\n",
    "\n",
    "1. train.csv: 训练数据，共7,377,418条记录\n",
    "\n",
    "    msno: 用户id，加密String  \n",
    "\n",
    "    song_id: song id，歌曲id\n",
    "\n",
    "    source_system_tab: 触发事件的类型/tab，用于表示app的功能类型\n",
    "\n",
    "    source_screen_name: 用户看到的布局的名字（name of the layout）\n",
    "\n",
    "    source_type: 用户在app上播放音乐的入口的类型\n",
    "\n",
    "    target: 标签。1表示用户在第一次听音乐后会在一个月内继续订阅，0表示没有订阅。\n",
    "\n",
    "2. test.csv ：测试数据，共2,556,790条记录\n",
    "\n",
    "    id: id (用于结果提交)\n",
    "\n",
    "    msno: 用户id\n",
    "\n",
    "    song_id: 歌曲id\n",
    "\n",
    "    source_system_tab: 触发事件的类型/tab，用于表示app的功能类型\n",
    "\n",
    "    source_screen_name: 用户看到的布局的名字（name of the layout）\n",
    "\n",
    "    source_type: 用户在app上播放音乐的入口的类型\n",
    "\n",
    "3. sampleSubmission.csv：提交结果文件样例  \n",
    "\n",
    "    提交测试结果包含两个字段，分别为测试样本id及其标签为1的概率，格式如下：\n",
    "\n",
    "    id,target\n",
    "    \n",
    "    2,0.3\n",
    "    \n",
    "    5,0.1\n",
    "    \n",
    "    6,1\n",
    "    \n",
    "    etc.\n",
    "\n",
    "4. songs.csv：歌曲元数据信息，用unicode编码\n",
    "\n",
    "    song_id：歌曲id\n",
    "\n",
    "    song_length: 单位为ms\n",
    "\n",
    "    genre_ids: genre 类别. 可多选，用 “|“隔开\n",
    "\n",
    "    artist_name：歌手\n",
    "\n",
    "    composer：作曲\n",
    "\n",
    "    lyricist：作词\n",
    "\n",
    "    language：语言\n",
    "\n",
    "5. members.csv：用户元数据信息\n",
    "\n",
    "    msno：用户id\n",
    "\n",
    "    city：城市\n",
    "\n",
    "    bd: 年龄。注意：年龄数据有离群点\n",
    "\n",
    "    gender：性别\n",
    "\n",
    "    registered_via: 注册方式\n",
    "\n",
    "    registration_init_time: 注册时间，格式为%Y%m%d\n",
    "\n",
    "    expiration_date: 到期时间，格式为 %Y%m%d\n",
    "\n",
    "6. song_extra_infos.csv：歌曲额外的信息\n",
    "\n",
    "    song_id：歌曲id\n",
    "\n",
    "    song name ：歌曲名字\n",
    "\n",
    "    isrc – 国际标准音像制品编码(International Standard Recording Code )。理论上可用于歌曲id，但产生的ISR没有经过官方授权。因此ISRC中的信息，如国家代码和参考年份可能不正确。且多首歌曲可能共享共一个ISRC，因为一首歌曲的音像制可发行多次。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 音乐数据合并（songs.csv + song_extra_info.csv）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "# 矩阵完整显示\n",
    "np.set_printoptions(threshold=np.inf)\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=</td>\n",
       "      <td>247640</td>\n",
       "      <td>465</td>\n",
       "      <td>張信哲 (Jeff Chang)</td>\n",
       "      <td>董貞</td>\n",
       "      <td>何啟弘</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=</td>\n",
       "      <td>197328</td>\n",
       "      <td>444</td>\n",
       "      <td>BLACKPINK</td>\n",
       "      <td>TEDDY|  FUTURE BOUNCE|  Bekuh BOOM</td>\n",
       "      <td>TEDDY</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=</td>\n",
       "      <td>231781</td>\n",
       "      <td>465</td>\n",
       "      <td>SUPER JUNIOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=</td>\n",
       "      <td>273554</td>\n",
       "      <td>465</td>\n",
       "      <td>S.H.E</td>\n",
       "      <td>湯小康</td>\n",
       "      <td>徐世珍</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=</td>\n",
       "      <td>140329</td>\n",
       "      <td>726</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>52.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id  song_length genre_ids  \\\n",
       "0  CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=       247640       465   \n",
       "1  o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=       197328       444   \n",
       "2  DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=       231781       465   \n",
       "3  dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=       273554       465   \n",
       "4  W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=       140329       726   \n",
       "\n",
       "        artist_name                            composer     lyricist  language  \n",
       "0  張信哲 (Jeff Chang)                                  董貞          何啟弘       3.0  \n",
       "1         BLACKPINK  TEDDY|  FUTURE BOUNCE|  Bekuh BOOM        TEDDY      31.0  \n",
       "2      SUPER JUNIOR                                 NaN          NaN      31.0  \n",
       "3             S.H.E                                 湯小康          徐世珍       3.0  \n",
       "4              貴族精選                         Traditional  Traditional      52.0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(2296833, 7)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>name</th>\n",
       "      <th>isrc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LP7pLJoJFBvyuUwvu+oLzjT+bI+UeBPURCecJsX1jjs=</td>\n",
       "      <td>我們</td>\n",
       "      <td>TWUM71200043</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ClazTFnk6r0Bnuie44bocdNMM3rdlrq0bCGAsGUWcHE=</td>\n",
       "      <td>Let Me Love You</td>\n",
       "      <td>QMZSY1600015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>u2ja/bZE3zhCGxvbbOB3zOoUjx27u40cf5g09UXMoKQ=</td>\n",
       "      <td>原諒我</td>\n",
       "      <td>TWA530887303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>92Fqsy0+p6+RHe2EoLKjHahORHR1Kq1TBJoClW9v+Ts=</td>\n",
       "      <td>Classic</td>\n",
       "      <td>USSM11301446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0QFmz/+rJy1Q56C1DuYqT9hKKqi5TUqx0sN0IwvoHrw=</td>\n",
       "      <td>愛投羅網</td>\n",
       "      <td>TWA471306001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id             name          isrc\n",
       "0  LP7pLJoJFBvyuUwvu+oLzjT+bI+UeBPURCecJsX1jjs=               我們  TWUM71200043\n",
       "1  ClazTFnk6r0Bnuie44bocdNMM3rdlrq0bCGAsGUWcHE=  Let Me Love You  QMZSY1600015\n",
       "2  u2ja/bZE3zhCGxvbbOB3zOoUjx27u40cf5g09UXMoKQ=              原諒我  TWA530887303\n",
       "3  92Fqsy0+p6+RHe2EoLKjHahORHR1Kq1TBJoClW9v+Ts=          Classic  USSM11301446\n",
       "4  0QFmz/+rJy1Q56C1DuYqT9hKKqi5TUqx0sN0IwvoHrw=             愛投羅網  TWA471306001"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(2296076, 3)"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dpath = './data/'\n",
    "\n",
    "df_songs_dataset = pd.read_csv(dpath +'songs.csv')\n",
    "df_songs_dataset.head()\n",
    "df_songs_dataset.shape\n",
    "\n",
    "df_songs_extra_dataset = pd.read_csv(dpath +'song_extra_info.csv')\n",
    "df_songs_extra_dataset.head()\n",
    "df_songs_extra_dataset.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**songs.csv的数据集大于song_extra_info.csv**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>name</th>\n",
       "      <th>isrc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [song_id, name, isrc]\n",
       "Index: []"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查找这2个文件是否存在重复行\n",
    "df_songs_extra_dataset[df_songs_extra_dataset.duplicated('song_id')==True]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [song_id, song_length, genre_ids, artist_name, composer, lyricist, language]\n",
       "Index: []"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_songs_dataset[df_songs_dataset.duplicated('song_id')==True]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**这2个文件中都没有重复的歌曲**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "df_songs_extra_dataset name null sum: 2\n"
     ]
    }
   ],
   "source": [
    "print('df_songs_extra_dataset name null sum:', df_songs_extra_dataset['name'].isna().sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "song_id         0\n",
       "name            2\n",
       "isrc       136548\n",
       "dtype: int64"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 检查song_extra_info每个特征缺失值的数量\n",
    "df_songs_extra_dataset.shape[0] - df_songs_extra_dataset.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "song_id              0\n",
       "song_length          0\n",
       "genre_ids        94116\n",
       "artist_name          0\n",
       "composer       1071354\n",
       "lyricist       1945268\n",
       "language             1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 检查songs每个特征缺失值的数量\n",
    "df_songs_dataset.shape[0] - df_songs_dataset.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 左连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>name</th>\n",
       "      <th>isrc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=</td>\n",
       "      <td>247640</td>\n",
       "      <td>465</td>\n",
       "      <td>張信哲 (Jeff Chang)</td>\n",
       "      <td>董貞</td>\n",
       "      <td>何啟弘</td>\n",
       "      <td>3.0</td>\n",
       "      <td>焚情</td>\n",
       "      <td>TWB531410010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=</td>\n",
       "      <td>197328</td>\n",
       "      <td>444</td>\n",
       "      <td>BLACKPINK</td>\n",
       "      <td>TEDDY|  FUTURE BOUNCE|  Bekuh BOOM</td>\n",
       "      <td>TEDDY</td>\n",
       "      <td>31.0</td>\n",
       "      <td>PLAYING WITH FIRE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=</td>\n",
       "      <td>231781</td>\n",
       "      <td>465</td>\n",
       "      <td>SUPER JUNIOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>SORRY| SORRY</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=</td>\n",
       "      <td>273554</td>\n",
       "      <td>465</td>\n",
       "      <td>S.H.E</td>\n",
       "      <td>湯小康</td>\n",
       "      <td>徐世珍</td>\n",
       "      <td>3.0</td>\n",
       "      <td>愛我的資格</td>\n",
       "      <td>TWC950206108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=</td>\n",
       "      <td>140329</td>\n",
       "      <td>726</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Mary Had a Little Lamb</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>kKJ2JNU5h8rphyW21ovC+RZU+yEHPM+3w85J37p7vEQ=</td>\n",
       "      <td>235520</td>\n",
       "      <td>864|857|850|843</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Joe Hisaishi</td>\n",
       "      <td>Hayao Miyazaki</td>\n",
       "      <td>17.0</td>\n",
       "      <td>となりのトトロ</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>N9vbanw7BSMoUgdfJlgX1aZPE1XZg8OS1wf88AQEcMc=</td>\n",
       "      <td>226220</td>\n",
       "      <td>458</td>\n",
       "      <td>伍佰 &amp; China Blue</td>\n",
       "      <td>Jonathan Lee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>夢醒時分</td>\n",
       "      <td>TWH951100012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>GsCpr618xfveHYJdo+E5SybrpR906tsjLMeKyrCNw8s=</td>\n",
       "      <td>276793</td>\n",
       "      <td>465</td>\n",
       "      <td>光良 (Michael Wong)</td>\n",
       "      <td>光良</td>\n",
       "      <td>彭資閔</td>\n",
       "      <td>3.0</td>\n",
       "      <td>記得我愛你</td>\n",
       "      <td>TWA450582110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>oTi7oINPX+rxoGp+3O6llSltQTl80jDqHoULfRoLcG4=</td>\n",
       "      <td>228623</td>\n",
       "      <td>465</td>\n",
       "      <td>林俊傑 (JJ Lin)</td>\n",
       "      <td>JJ Lin</td>\n",
       "      <td>Wu Qing Feng</td>\n",
       "      <td>3.0</td>\n",
       "      <td>裂縫中的陽光 (Before Sunrise)</td>\n",
       "      <td>TWA531398021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>btcG03OHY3GNKWccPP0auvtSbhxog/kllIIOx5grE/k=</td>\n",
       "      <td>232629</td>\n",
       "      <td>352|1995</td>\n",
       "      <td>Kodaline</td>\n",
       "      <td>Stephen Garrigan| Mark Prendergast| Vincent Ma...</td>\n",
       "      <td>Stephen Garrigan| Mark Prendergast| Vincent Ma...</td>\n",
       "      <td>52.0</td>\n",
       "      <td>The One</td>\n",
       "      <td>GBARL1401580</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id  song_length        genre_ids  \\\n",
       "0  CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=       247640              465   \n",
       "1  o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=       197328              444   \n",
       "2  DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=       231781              465   \n",
       "3  dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=       273554              465   \n",
       "4  W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=       140329              726   \n",
       "5  kKJ2JNU5h8rphyW21ovC+RZU+yEHPM+3w85J37p7vEQ=       235520  864|857|850|843   \n",
       "6  N9vbanw7BSMoUgdfJlgX1aZPE1XZg8OS1wf88AQEcMc=       226220              458   \n",
       "7  GsCpr618xfveHYJdo+E5SybrpR906tsjLMeKyrCNw8s=       276793              465   \n",
       "8  oTi7oINPX+rxoGp+3O6llSltQTl80jDqHoULfRoLcG4=       228623              465   \n",
       "9  btcG03OHY3GNKWccPP0auvtSbhxog/kllIIOx5grE/k=       232629         352|1995   \n",
       "\n",
       "         artist_name                                           composer  \\\n",
       "0   張信哲 (Jeff Chang)                                                 董貞   \n",
       "1          BLACKPINK                 TEDDY|  FUTURE BOUNCE|  Bekuh BOOM   \n",
       "2       SUPER JUNIOR                                                NaN   \n",
       "3              S.H.E                                                湯小康   \n",
       "4               貴族精選                                        Traditional   \n",
       "5               貴族精選                                       Joe Hisaishi   \n",
       "6    伍佰 & China Blue                                       Jonathan Lee   \n",
       "7  光良 (Michael Wong)                                                 光良   \n",
       "8       林俊傑 (JJ Lin)                                             JJ Lin   \n",
       "9           Kodaline  Stephen Garrigan| Mark Prendergast| Vincent Ma...   \n",
       "\n",
       "                                            lyricist  language  \\\n",
       "0                                                何啟弘       3.0   \n",
       "1                                              TEDDY      31.0   \n",
       "2                                                NaN      31.0   \n",
       "3                                                徐世珍       3.0   \n",
       "4                                        Traditional      52.0   \n",
       "5                                     Hayao Miyazaki      17.0   \n",
       "6                                                NaN       3.0   \n",
       "7                                                彭資閔       3.0   \n",
       "8                                       Wu Qing Feng       3.0   \n",
       "9  Stephen Garrigan| Mark Prendergast| Vincent Ma...      52.0   \n",
       "\n",
       "                      name          isrc  \n",
       "0                       焚情  TWB531410010  \n",
       "1        PLAYING WITH FIRE           NaN  \n",
       "2             SORRY| SORRY           NaN  \n",
       "3                    愛我的資格  TWC950206108  \n",
       "4   Mary Had a Little Lamb           NaN  \n",
       "5                  となりのトトロ           NaN  \n",
       "6                     夢醒時分  TWH951100012  \n",
       "7                    記得我愛你  TWA450582110  \n",
       "8  裂縫中的陽光 (Before Sunrise)  TWA531398021  \n",
       "9                  The One  GBARL1401580  "
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据合并--左链接\n",
    "df_songs_left_full_dataset = pd.merge(df_songs_dataset, df_songs_extra_dataset, how='left', on='song_id', sort=False)\n",
    "df_songs_left_full_dataset.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2296320, 9)"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看数据合并后的数据集大小\n",
    "df_songs_left_full_dataset.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 统计每个特征缺失值的数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "song_id              0\n",
       "song_length          0\n",
       "genre_ids        94116\n",
       "artist_name          0\n",
       "composer       1071354\n",
       "lyricist       1945268\n",
       "language             1\n",
       "name               900\n",
       "isrc            137428\n",
       "dtype: int64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_songs_left_full_dataset.shape[0] - df_songs_left_full_dataset.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "* **作曲（composer）和作词（lyricist）数据缺失严重！歌曲名称name为什么左连接后会有那么多缺失值？但是经过验证，原始数据集中这些name是有值的，难道merge方法有问题？下面分别试试右连接和内连接的情况**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>name</th>\n",
       "      <th>isrc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>20762</th>\n",
       "      <td>t+rt6dnHwaoDkelR7CRxtr0TucfQSDbMvPkh0+8BiS4=</td>\n",
       "      <td>348485</td>\n",
       "      <td>1259</td>\n",
       "      <td>Soulja Boy Tellem</td>\n",
       "      <td>D. Way</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20920</th>\n",
       "      <td>TVtjw6v0EWL0nGOWkYsZ3IgUJa/ZMWahvEisor37Gzk=</td>\n",
       "      <td>598796</td>\n",
       "      <td>921</td>\n",
       "      <td>Nigel Stanford</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20965</th>\n",
       "      <td>rYtSdAS6UGjee8zqmg3WDQMQ0I7cQbiKqtqNPKqbO+U=</td>\n",
       "      <td>230620</td>\n",
       "      <td>1609</td>\n",
       "      <td>Various Artists</td>\n",
       "      <td>JOHAN JENS ERIK CARLSSON| ROSS JACOB GOLAN| MA...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54927</th>\n",
       "      <td>3wh/+zfi1qUHigu3M9HY5GQRpcEBzw8yqUve0W4ppj0=</td>\n",
       "      <td>214738</td>\n",
       "      <td>465</td>\n",
       "      <td>小妮妮</td>\n",
       "      <td>佚名</td>\n",
       "      <td>NaN</td>\n",
       "      <td>59.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83231</th>\n",
       "      <td>EqG1FQ2ZMDgqBC8vnSCTqN+TneeuQuSqKnljU2W9f44=</td>\n",
       "      <td>209528</td>\n",
       "      <td>465</td>\n",
       "      <td>奇克拿樂團 (ChicKNUP Band)</td>\n",
       "      <td>陳菡潔</td>\n",
       "      <td>陳菡潔</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            song_id  song_length genre_ids  \\\n",
       "20762  t+rt6dnHwaoDkelR7CRxtr0TucfQSDbMvPkh0+8BiS4=       348485      1259   \n",
       "20920  TVtjw6v0EWL0nGOWkYsZ3IgUJa/ZMWahvEisor37Gzk=       598796       921   \n",
       "20965  rYtSdAS6UGjee8zqmg3WDQMQ0I7cQbiKqtqNPKqbO+U=       230620      1609   \n",
       "54927  3wh/+zfi1qUHigu3M9HY5GQRpcEBzw8yqUve0W4ppj0=       214738       465   \n",
       "83231  EqG1FQ2ZMDgqBC8vnSCTqN+TneeuQuSqKnljU2W9f44=       209528       465   \n",
       "\n",
       "                 artist_name  \\\n",
       "20762      Soulja Boy Tellem   \n",
       "20920         Nigel Stanford   \n",
       "20965        Various Artists   \n",
       "54927                    小妮妮   \n",
       "83231  奇克拿樂團 (ChicKNUP Band)   \n",
       "\n",
       "                                                composer lyricist  language  \\\n",
       "20762                                             D. Way      NaN      52.0   \n",
       "20920                                                NaN      NaN      52.0   \n",
       "20965  JOHAN JENS ERIK CARLSSON| ROSS JACOB GOLAN| MA...      NaN      52.0   \n",
       "54927                                                 佚名      NaN      59.0   \n",
       "83231                                                陳菡潔      陳菡潔      52.0   \n",
       "\n",
       "      name isrc  \n",
       "20762  NaN  NaN  \n",
       "20920  NaN  NaN  \n",
       "20965  NaN  NaN  \n",
       "54927  NaN  NaN  \n",
       "83231  NaN  NaN  "
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查找name=null的数据\n",
    "df_songs_left_full_dataset[df_songs_left_full_dataset['name'].isnull().values==True].head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 右连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>name</th>\n",
       "      <th>isrc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=</td>\n",
       "      <td>247640.0</td>\n",
       "      <td>465</td>\n",
       "      <td>張信哲 (Jeff Chang)</td>\n",
       "      <td>董貞</td>\n",
       "      <td>何啟弘</td>\n",
       "      <td>3.0</td>\n",
       "      <td>焚情</td>\n",
       "      <td>TWB531410010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=</td>\n",
       "      <td>197328.0</td>\n",
       "      <td>444</td>\n",
       "      <td>BLACKPINK</td>\n",
       "      <td>TEDDY|  FUTURE BOUNCE|  Bekuh BOOM</td>\n",
       "      <td>TEDDY</td>\n",
       "      <td>31.0</td>\n",
       "      <td>PLAYING WITH FIRE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=</td>\n",
       "      <td>231781.0</td>\n",
       "      <td>465</td>\n",
       "      <td>SUPER JUNIOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>SORRY| SORRY</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=</td>\n",
       "      <td>273554.0</td>\n",
       "      <td>465</td>\n",
       "      <td>S.H.E</td>\n",
       "      <td>湯小康</td>\n",
       "      <td>徐世珍</td>\n",
       "      <td>3.0</td>\n",
       "      <td>愛我的資格</td>\n",
       "      <td>TWC950206108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=</td>\n",
       "      <td>140329.0</td>\n",
       "      <td>726</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Mary Had a Little Lamb</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>kKJ2JNU5h8rphyW21ovC+RZU+yEHPM+3w85J37p7vEQ=</td>\n",
       "      <td>235520.0</td>\n",
       "      <td>864|857|850|843</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Joe Hisaishi</td>\n",
       "      <td>Hayao Miyazaki</td>\n",
       "      <td>17.0</td>\n",
       "      <td>となりのトトロ</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>N9vbanw7BSMoUgdfJlgX1aZPE1XZg8OS1wf88AQEcMc=</td>\n",
       "      <td>226220.0</td>\n",
       "      <td>458</td>\n",
       "      <td>伍佰 &amp; China Blue</td>\n",
       "      <td>Jonathan Lee</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "      <td>夢醒時分</td>\n",
       "      <td>TWH951100012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>GsCpr618xfveHYJdo+E5SybrpR906tsjLMeKyrCNw8s=</td>\n",
       "      <td>276793.0</td>\n",
       "      <td>465</td>\n",
       "      <td>光良 (Michael Wong)</td>\n",
       "      <td>光良</td>\n",
       "      <td>彭資閔</td>\n",
       "      <td>3.0</td>\n",
       "      <td>記得我愛你</td>\n",
       "      <td>TWA450582110</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>oTi7oINPX+rxoGp+3O6llSltQTl80jDqHoULfRoLcG4=</td>\n",
       "      <td>228623.0</td>\n",
       "      <td>465</td>\n",
       "      <td>林俊傑 (JJ Lin)</td>\n",
       "      <td>JJ Lin</td>\n",
       "      <td>Wu Qing Feng</td>\n",
       "      <td>3.0</td>\n",
       "      <td>裂縫中的陽光 (Before Sunrise)</td>\n",
       "      <td>TWA531398021</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>btcG03OHY3GNKWccPP0auvtSbhxog/kllIIOx5grE/k=</td>\n",
       "      <td>232629.0</td>\n",
       "      <td>352|1995</td>\n",
       "      <td>Kodaline</td>\n",
       "      <td>Stephen Garrigan| Mark Prendergast| Vincent Ma...</td>\n",
       "      <td>Stephen Garrigan| Mark Prendergast| Vincent Ma...</td>\n",
       "      <td>52.0</td>\n",
       "      <td>The One</td>\n",
       "      <td>GBARL1401580</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id  song_length        genre_ids  \\\n",
       "0  CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=     247640.0              465   \n",
       "1  o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=     197328.0              444   \n",
       "2  DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=     231781.0              465   \n",
       "3  dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=     273554.0              465   \n",
       "4  W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=     140329.0              726   \n",
       "5  kKJ2JNU5h8rphyW21ovC+RZU+yEHPM+3w85J37p7vEQ=     235520.0  864|857|850|843   \n",
       "6  N9vbanw7BSMoUgdfJlgX1aZPE1XZg8OS1wf88AQEcMc=     226220.0              458   \n",
       "7  GsCpr618xfveHYJdo+E5SybrpR906tsjLMeKyrCNw8s=     276793.0              465   \n",
       "8  oTi7oINPX+rxoGp+3O6llSltQTl80jDqHoULfRoLcG4=     228623.0              465   \n",
       "9  btcG03OHY3GNKWccPP0auvtSbhxog/kllIIOx5grE/k=     232629.0         352|1995   \n",
       "\n",
       "         artist_name                                           composer  \\\n",
       "0   張信哲 (Jeff Chang)                                                 董貞   \n",
       "1          BLACKPINK                 TEDDY|  FUTURE BOUNCE|  Bekuh BOOM   \n",
       "2       SUPER JUNIOR                                                NaN   \n",
       "3              S.H.E                                                湯小康   \n",
       "4               貴族精選                                        Traditional   \n",
       "5               貴族精選                                       Joe Hisaishi   \n",
       "6    伍佰 & China Blue                                       Jonathan Lee   \n",
       "7  光良 (Michael Wong)                                                 光良   \n",
       "8       林俊傑 (JJ Lin)                                             JJ Lin   \n",
       "9           Kodaline  Stephen Garrigan| Mark Prendergast| Vincent Ma...   \n",
       "\n",
       "                                            lyricist  language  \\\n",
       "0                                                何啟弘       3.0   \n",
       "1                                              TEDDY      31.0   \n",
       "2                                                NaN      31.0   \n",
       "3                                                徐世珍       3.0   \n",
       "4                                        Traditional      52.0   \n",
       "5                                     Hayao Miyazaki      17.0   \n",
       "6                                                NaN       3.0   \n",
       "7                                                彭資閔       3.0   \n",
       "8                                       Wu Qing Feng       3.0   \n",
       "9  Stephen Garrigan| Mark Prendergast| Vincent Ma...      52.0   \n",
       "\n",
       "                      name          isrc  \n",
       "0                       焚情  TWB531410010  \n",
       "1        PLAYING WITH FIRE           NaN  \n",
       "2             SORRY| SORRY           NaN  \n",
       "3                    愛我的資格  TWC950206108  \n",
       "4   Mary Had a Little Lamb           NaN  \n",
       "5                  となりのトトロ           NaN  \n",
       "6                     夢醒時分  TWH951100012  \n",
       "7                    記得我愛你  TWA450582110  \n",
       "8  裂縫中的陽光 (Before Sunrise)  TWA531398021  \n",
       "9                  The One  GBARL1401580  "
      ]
     },
     "execution_count": 71,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据合并--右连接\n",
    "df_songs_right_full_dataset = pd.merge(df_songs_dataset, df_songs_extra_dataset, how='right', on='song_id', sort=False)\n",
    "df_songs_right_full_dataset.head(10)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2295971, 9)"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看数据合并后的数据集大小\n",
    "df_songs_right_full_dataset.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 统计每个特征缺失值的数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "song_id              0\n",
       "song_length        549\n",
       "genre_ids        94622\n",
       "artist_name        549\n",
       "composer       1071487\n",
       "lyricist       1945072\n",
       "language           550\n",
       "name                 2\n",
       "isrc            136548\n",
       "dtype: int64"
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_songs_right_full_dataset.shape[0] - df_songs_right_full_dataset.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>name</th>\n",
       "      <th>isrc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>83227</th>\n",
       "      <td>EqG1FQ2ZMDgqBC8vnSCTqN+TneeuQuSqKnljU2W9f44=</td>\n",
       "      <td>209528.0</td>\n",
       "      <td>465</td>\n",
       "      <td>奇克拿樂團 (ChicKNUP Band)</td>\n",
       "      <td>陳菡潔</td>\n",
       "      <td>陳菡潔</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185407</th>\n",
       "      <td>sNVAWeE2/q4auIOdlGc2H3WT2bw99rgk95+MPh81S84=</td>\n",
       "      <td>243552.0</td>\n",
       "      <td>465|139|125|109</td>\n",
       "      <td>Space Cake 史貝絲考克</td>\n",
       "      <td>周勝宏</td>\n",
       "      <td>周勝宏</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TWAE31500124</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             song_id  song_length  \\\n",
       "83227   EqG1FQ2ZMDgqBC8vnSCTqN+TneeuQuSqKnljU2W9f44=     209528.0   \n",
       "185407  sNVAWeE2/q4auIOdlGc2H3WT2bw99rgk95+MPh81S84=     243552.0   \n",
       "\n",
       "              genre_ids            artist_name composer lyricist  language  \\\n",
       "83227               465  奇克拿樂團 (ChicKNUP Band)      陳菡潔      陳菡潔      52.0   \n",
       "185407  465|139|125|109       Space Cake 史貝絲考克      周勝宏      周勝宏       3.0   \n",
       "\n",
       "       name          isrc  \n",
       "83227   NaN           NaN  \n",
       "185407  NaN  TWAE31500124  "
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查找name=null的数据\n",
    "df_songs_right_full_dataset[df_songs_right_full_dataset['name'].isnull().values==True].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**经过手工验证，上面2条数据在原始数据集中的确是空值。但是为什么歌曲长度song_length会有那么多的缺失值？而在上面的左连接中没有缺失值！下面验证一下这些缺失值的结果在原始数据集中是否真实存在**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>name</th>\n",
       "      <th>isrc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [song_id, song_length, genre_ids, artist_name, composer, lyricist, language, name, isrc]\n",
       "Index: []"
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查找song_length=null的数据\n",
    "df_song_length_null_dataset = df_songs_right_full_dataset[df_songs_right_full_dataset['song_length'].isnull().values==True]\n",
    "# 判断这些song_id是否在原始数据中存在\n",
    "df_songs_dataset[df_songs_dataset['song_id'].isin(pd.Series(df_song_length_null_dataset['song_id']))]\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**上面的代码显示结果是不存在的。但是经过手工验证，上面这些缺失值的歌曲，在原始数据集中根本就是存在的！为什么丢失了呢？真的很奇怪！下面看看内连接的结果**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 内连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>name</th>\n",
       "      <th>isrc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=</td>\n",
       "      <td>247640</td>\n",
       "      <td>465</td>\n",
       "      <td>張信哲 (Jeff Chang)</td>\n",
       "      <td>董貞</td>\n",
       "      <td>何啟弘</td>\n",
       "      <td>3.0</td>\n",
       "      <td>焚情</td>\n",
       "      <td>TWB531410010</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=</td>\n",
       "      <td>197328</td>\n",
       "      <td>444</td>\n",
       "      <td>BLACKPINK</td>\n",
       "      <td>TEDDY|  FUTURE BOUNCE|  Bekuh BOOM</td>\n",
       "      <td>TEDDY</td>\n",
       "      <td>31.0</td>\n",
       "      <td>PLAYING WITH FIRE</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=</td>\n",
       "      <td>231781</td>\n",
       "      <td>465</td>\n",
       "      <td>SUPER JUNIOR</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>31.0</td>\n",
       "      <td>SORRY| SORRY</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=</td>\n",
       "      <td>273554</td>\n",
       "      <td>465</td>\n",
       "      <td>S.H.E</td>\n",
       "      <td>湯小康</td>\n",
       "      <td>徐世珍</td>\n",
       "      <td>3.0</td>\n",
       "      <td>愛我的資格</td>\n",
       "      <td>TWC950206108</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=</td>\n",
       "      <td>140329</td>\n",
       "      <td>726</td>\n",
       "      <td>貴族精選</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>Traditional</td>\n",
       "      <td>52.0</td>\n",
       "      <td>Mary Had a Little Lamb</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                        song_id  song_length genre_ids  \\\n",
       "0  CXoTN1eb7AI+DntdU1vbcwGRV4SCIDxZu+YD8JP8r4E=       247640       465   \n",
       "1  o0kFgae9QtnYgRkVPqLJwa05zIhRlUjfF7O1tDw0ZDU=       197328       444   \n",
       "2  DwVvVurfpuz+XPuFvucclVQEyPqcpUkHR0ne1RQzPs0=       231781       465   \n",
       "3  dKMBWoZyScdxSkihKG+Vf47nc18N9q4m58+b4e7dSSE=       273554       465   \n",
       "4  W3bqWd3T+VeHFzHAUfARgW9AvVRaF4N5Yzm4Mr6Eo/o=       140329       726   \n",
       "\n",
       "        artist_name                            composer     lyricist  \\\n",
       "0  張信哲 (Jeff Chang)                                  董貞          何啟弘   \n",
       "1         BLACKPINK  TEDDY|  FUTURE BOUNCE|  Bekuh BOOM        TEDDY   \n",
       "2      SUPER JUNIOR                                 NaN          NaN   \n",
       "3             S.H.E                                 湯小康          徐世珍   \n",
       "4              貴族精選                         Traditional  Traditional   \n",
       "\n",
       "   language                    name          isrc  \n",
       "0       3.0                      焚情  TWB531410010  \n",
       "1      31.0       PLAYING WITH FIRE           NaN  \n",
       "2      31.0            SORRY| SORRY           NaN  \n",
       "3       3.0                   愛我的資格  TWC950206108  \n",
       "4      52.0  Mary Had a Little Lamb           NaN  "
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 数据合并--内连接\n",
    "df_songs_inner_full_dataset = pd.merge(df_songs_dataset, df_songs_extra_dataset, how='inner', on='song_id', sort=False)\n",
    "df_songs_inner_full_dataset.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2295422, 9)"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看数据合并后的数据集大小\n",
    "df_songs_inner_full_dataset.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 统计每个特征缺失值的数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "song_id              0\n",
       "song_length          0\n",
       "genre_ids        94073\n",
       "artist_name          0\n",
       "composer       1070938\n",
       "lyricist       1944523\n",
       "language             1\n",
       "name                 2\n",
       "isrc            136530\n",
       "dtype: int64"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_songs_inner_full_dataset.shape[0] - df_songs_inner_full_dataset.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>song_id</th>\n",
       "      <th>song_length</th>\n",
       "      <th>genre_ids</th>\n",
       "      <th>artist_name</th>\n",
       "      <th>composer</th>\n",
       "      <th>lyricist</th>\n",
       "      <th>language</th>\n",
       "      <th>name</th>\n",
       "      <th>isrc</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>83227</th>\n",
       "      <td>EqG1FQ2ZMDgqBC8vnSCTqN+TneeuQuSqKnljU2W9f44=</td>\n",
       "      <td>209528</td>\n",
       "      <td>465</td>\n",
       "      <td>奇克拿樂團 (ChicKNUP Band)</td>\n",
       "      <td>陳菡潔</td>\n",
       "      <td>陳菡潔</td>\n",
       "      <td>52.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185407</th>\n",
       "      <td>sNVAWeE2/q4auIOdlGc2H3WT2bw99rgk95+MPh81S84=</td>\n",
       "      <td>243552</td>\n",
       "      <td>465|139|125|109</td>\n",
       "      <td>Space Cake 史貝絲考克</td>\n",
       "      <td>周勝宏</td>\n",
       "      <td>周勝宏</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>TWAE31500124</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             song_id  song_length  \\\n",
       "83227   EqG1FQ2ZMDgqBC8vnSCTqN+TneeuQuSqKnljU2W9f44=       209528   \n",
       "185407  sNVAWeE2/q4auIOdlGc2H3WT2bw99rgk95+MPh81S84=       243552   \n",
       "\n",
       "              genre_ids            artist_name composer lyricist  language  \\\n",
       "83227               465  奇克拿樂團 (ChicKNUP Band)      陳菡潔      陳菡潔      52.0   \n",
       "185407  465|139|125|109       Space Cake 史貝絲考克      周勝宏      周勝宏       3.0   \n",
       "\n",
       "       name          isrc  \n",
       "83227   NaN           NaN  \n",
       "185407  NaN  TWAE31500124  "
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查找name=null的数据\n",
    "df_songs_inner_full_dataset[df_songs_inner_full_dataset['name'].isnull().values==True].head(20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**经过内连接后的结果，上面显示的缺失值与原始数据集一致，但是整个数据集的大小变成了2295422，与songs.csv(2296834)相差1412个样本，与song_extra_info.csv(2296869)相差1447个样本。这样会导致train.csv会缺失很多数据。所以问题应该出自于原始数据集！**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 坑！！\n",
    "\n",
    "**原本想对songs.csv和song_extra_info.csv进行数据合并，后来通过read_csv()读入的数据跟实际数据集大小相差几百个。原以为原始数据集没问题，就继续对数据分别进行左连接，右连接和内连接合并，最后发现都有问题：**\n",
    "\n",
    "**（1）左连接：song_length（歌曲长度）缺失严重，但是经过手工验证，发现原始数据集中根本没有缺失**\n",
    "\n",
    "**（2）右连接：name（歌曲名称）缺失严重，同样经过手工验证，这些数据也没有缺失**\n",
    "\n",
    "**（3）内连接：虽然song_length和name的缺失值跟原始数据一致，但是数据集变得更少了，大概少了1400多条**\n",
    "\n",
    "**于是，我开始怀疑是pandas的问题，后面经过李伟同学的指点发现，是原始数据集中的特殊字符（双引号或者单引号）导致的，如下面这条记录：【IBbV7L+0kPnbIUX8L6ltsmUmiSTp3fm3yD8yRt078FQ=,\"Salve Maria de Jerusalem (I Lombardi alla prima crociata) S.431,ITB001300266】**\n",
    "\n",
    "* **解决办法：先对原始数据集进行特殊字符处理，在读入到pandas中。** "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 出问题的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Salve Maria de Jerusalem (I Lombardi alla prima crociata) S.431,ITB001300266\\nGuBNs85V1jOVG8CMB5OjR0xtQPJyVAqiYS+LHrQoj9Q=,Laissez - Moi Tranquille,DEPZ68838066\\nS4n+vHTSlCzynEDQp/4v4H2r3S+EHVguVHTt9vhCLUo=,Oh Happy Day,ES7581300564\\n26pjj+hnK2pqCy3rs/XyOYTo+XFSLGF5emOdnsrahsQ=,Circle Of Light,FISHQ1600001\\n1E6TItr3OhrrQg2O4YziuKGhfCMtb7F5IGTfQXP2K84=,It\\'s Only Make Believe,USA561394938\\ni7Dx/4RdaU0d56NT+a8r+fIdnAT27dzDZQpttuObo3c=,Perkins Wiggle,GBBXS1157068\\nC0r6iwio0u1TeDKER9bTIDKhKyyCPMbc98ptMo3fIgg=,Mercy,USCBK0911459\\na2xFv5G9/oaUTpc3SCMKqQhydMq5LrZab+eqAOIpWlI=,Suite No. 2 in D Minor| BWV 1008: Prelude,NLCT21404008\\ns5D7mJlKIebOzjx2sSa0xEbawEeZWHMDHK2lM6jwIlg=,Balloons,DEBE71300015\\n2bbYfpeGo24/ttNlXBlgnCbXRlHFESN6qvl4Y/5o5PY=,Let You Cuff Her,TCACD1584864\\nSI1slp0YkmpxniEQ1g1qPURLqbgkX7F3fX9ylBGCLuM=,Milestones,US3M80400470\\nvt/RjOZSrgbZC/hXzbmMk/IhJ+87syUBwGfQR3yNXrg=,Auld Lang Syne,USTB10300964\\nPu5scvSotMAkrP3kpH7F9X0tsmx7yAOfaqloOSFTH34=,Variations| Interlude et Finale sur un theme de Rameau,JPL251600158\\nCDvTGsxSVI11jlVv55UFFbptVmj+oWlNDg8Em04lAog=,I See Colours,GBLVL0600053\\nxbH9fXSEuoXCGM9R9yeHY2ZLIlbwWKUBCNEVtNatCBY=,Supergirl (Outro),SEAYD0002140\\n3WtdnTyi8NDJ0jEpMw0VmeLRgOysu10ZIbg7c5OYYH8=,Mystic Temple,US2H51010062\\nWNXYz4E8iJ8bXhSGKLSShH/eTxtz674Ls7TXTl6M5yw=,Rockin\\' Around the Christmas Tree,FR0W69936393\\ndtZV1X9oI7LB6RkrQf1wLd+SlVCSCRy46aGle92GT4c=,歌舞今宵,HKB129832128\\n+obmx+neaOqi4XoIc3sUiVRHFw2RsycnzwkCdYuPPrE=,鳳仇恩閣,\\n//tXfPhf99BxJgJwGp6enGfH7mg524WsOw+kxal4WYg=,Nocturne,CASD10607056\\nRBvqpfXQkCmoIczSy/Mo1Wz4hIEiYZV1Nm33xlElMlo=,Tonight,USRN10200789\\nhDdmIqEzmVWOX1+s0hdy7wzzyMQollk+i/HGCWDjdYY=,The Great Valerio,GBAAN7400103\\ntB0v+cxWcmobmmsI6ZV8QlCAGElvCtWeivJgCBg1tv8=,Marinheiro Só,BRQUI0300016\\nVju34uj4trwvlJFnIQsr1RHh2bKuV7ZUnRXtZM41mNY=,Vigil,USA370397331\\nmkjnVz3MVdv09spiH8Xo2EjJSxSgi+ypm2/g4cFdVgs=,G Major,GBJSN0904423\\nR5D6iBUwy2L3q6WPMSavaz7UbTI7AdSBCZbgzjGLyxE=,Living On The Edges Of The World,AUEV40900008\\nveYgd1wNdiImIhHp3GoHDCxo0pSGf4t7ss/XALH4Axg=,Lord| you made me so weak,BEI010600177\\n5OFu1mviLjVfvU/INTh5vPoMY9bWMqECOw63ZPYKS+w=,Payday,TCABK1223813\\n229pQI3xNv+sLmc4F1IC/PzFP7wSbap0HirlqG2ktfw=,Žárlivá,CZP291600008\\nCzpuKtK8gUZv4rQdpi8J8Dedxqnt22YKeDYbIgmwTzM=,Ain´t No Bluesman,DESG11400004\\nKa1jBJtMzymaBxsmIjeB9M4kJSxGS+96Ub3qBzXwh8g=,The Light,DEW871505168\\nw8/NAOf7aGM1WcgQecVQw848feQ9W+lfF+dTa2TtM6s=,No Turning Back,ES5050101175\\nnyAqN6AFF7754qbpv/DYf6ID32Z1mlQF4YvYGMos9Z0=,Jhonny B. Goode,ARA821000923\\nXyMpw+mKXJ8uPb8l179yNlLSP+pSxs7aa5FBolSyBnE=,Thanks to You (Originally Performed by Tyler Collins) [Karaoke Version],USK4W0720576\\nUTytyCuaRph6iogaou1OQl+VFQLvk7xcK1Fgyvztrvk=,How Long (Has This Been Goin\\' On),USSM17600321\\ndr9LiTnbtNvgML7Tnuaghc0m5qSLjpenTOzOa2UHor8=,I\\'ll Rember April,DEU240620859\\nHNYLEJqfUD0fPMNNe86QaZ0g4saXkw9GZzJn++hBg+Q=,武则天秘史,FR59R1688042\\n7QWLOOIEFxHrZAvbknLcwGpNPHW2HafILjbhma3YUyk=,Flash Bang,USDM31602601\\nDZRhf8LDkcH/S5ESd6PZMV5zEXAD9TbdjLWDZBSz0VM=,Kansas City,DEPZ69758345\\nk9hgdn86LEr1tiZsIuluSOS49kox5xxf2n5SbWI3QIw=,Missing You Like Crazy (미치게 보고싶은) [Originally Performed By Tae Yeon],KRB361211276\\nguSrcCQBjVjReSzC5jt7j2Y3bP7QZgp1on1Lz4sj2Es=,Back to Cali,DEW871603233\\nmXjqlejDUIFj+WhR0F1pNTVf/0k39siKTy9crtodCDA=,You Don\\'t Know What Love Is,CAE150300039\\nI++4keidOBtDmFgW5orFGiLIPCdO07qo+/aCMdMgww0=,A Froggie Went A\\'Courtin\\',USA561047659\\nemrWEaQ2QCri5t24RjJ328yHgcjIcVaPkKbLvdUz7Fk=,The Odds,DEEF11200034\\nJsOSJGyDAXlDE8XxSBNS5ANvENIHgb5g8ud9qwINBY8=,Be Fine (feat. Michele Wylen),TCACM1686465\\ngSJcTwF0AB2PYakAVO4FQIrNbiRpiR2QZO1X/ttHHGc=,Cold,US6R21302000\\nv8S/s5bnUPGHnYHcDlzXjPJpeXfIWLS1q8ETrLkmgDE=,Electric Earth,DEMW51602074\\nNP7dMxNo1fi4SbMfvlsatDrJoN//5y3q9XDjWg7JHMQ=,อยู่ไหนก็ไม่เหงา,THG010823805\\nXP1MDM+yRbuFomdKke9vZmIH4Fi9uDw/5vpJ+n9c8EA=,Quer,USCPT1510308\\n20QjceWCUm4qw/Z5kc06yfAAFykRcqNudI3/aHO2tso=,Blue Skies,BEDO61500444\\nvTpWOLvNEdq2mb5YcFloY5DR9NJ3LCbS3iE6AI7i/yU=,Deep End (Deep Deep),TCACS1679724\\ntCeV8NEhnlcf5QvsVn3JJ9SzusJIjCqmvoJP3/yMJM0=,Year Of The Guru,USF096825470\\nyxYcB4lHLrhPPz/0jY+UF6Xj4YwLVS1LVTyghOnCpJQ=,NESTS Body Transformation Project Theme <Demo Ver2.0> (Interrim Demo 2),\\nHblro9APv7nMou3gwBn0HGNQGzMTJ7As90mTW2Q2Ngk=,Encarcelado,AR6VD0300001\\nBZf7mPM83quPeecuvWrI1FtUbRL/KiKpMljUaTAUJE4=,Baby Call On Me,DEPZ69721678\\nsppC1e3rRPUv9QL0bJdlQ+Is4wONcvauM0VPin2Ohy8=,TELL IT LIKE IT IS,USSM11003481\\nUxNSegNMfBQq0jdvJ1yoFR8fEwv27XFtWcRO3Tg5jjs=,El Chochlo,DEPZ68854193\\nDiVFFpSyEK+eMzFvjnjdpUa95nF+xRLOXXz4UgXoaEc=,靈動(演唱版),\\nL+fFJ6tgEjt1ZXmb7eElxqyAgUBLGPLuiQO5mJntEfM=,LEGO NINJAGO - THE GHOST WHIP,DKTG61100143\\n6IfNz7KLD+LlvW/xNlhJ2dOhmIxRiCI7UakgSUhz2PQ=,Minus the T (It Will Never Happen Again),TCACM1622800\\n8MUtlL6fcmjix6vLXvuJ9+HSXUNkmuDxfkMn2reL9Nk=,Princess of the Night,DEU600600474\\nd2eLYSdCC/XD11EYK/jZvWY1giFrroeqkB+sSLFJ2dA=,Quédate este bolero,ESAAI0208344\\nC8PD57BEs8txuRTuRsaebndVtcIYa+A8+WROX8VDt3o=,如果有一天,\\nceY7QvZWRDQEMTa7eSBKlyDD3SMY9JfUFU2lJgfXPDM=,Ave Maria,FRZ810611970\\n31TD195CzjqfjPNS/yltllUcne6jGjoWHKm+2pxTEo8=,IV. Allegro vivace,DEE470200039\\nhzu/oAwjG7mtXqV9Z4PZ/v+BiqVf8QryHa8pirV6hXU=,못잊어 Cannot Forget,KRA531008008\\nJqUpypRVsPI5hcpP/qwSVi8mr1h17n2D5kqU7GfhrHM=,SMS Saja,MYSM10300015\\n8VstL387fIepVs5AMCMU56BM1mY+xdXqosXLGMfHZNg=,Wave Intruder,GBAMY9700143\\nt3TiPpk1QTyBNQmuQzNRz2w48hobEjmdEzzqXpNSqww=,Stella del sud,ITO151000373\\nMS2C/AHW5D3zU06tze6aw7ML0BVHR9cVtFTEPLps1Rc=,Carson Lake,US5UL1237553\\nqyt6yGzcmQbWVFlh/EASVyAS/YDNQXMfRWXx/tRGhfc=,Stevie,USMC16352621\\nW0N+cPxFrHRhdTEmvpsi+7KwdskUfLpg8vGqWt3Lz9Q=,Cursa (Nujabes Tribute),QM7281431088\\nh6sGo8dxrrCeTQIdL7k+IMQ/GgMBBLiKEZId/IkHS50=,Organ Concerto in D Minor| Op. 7 No. 4| HWV 309: III. Organo ad libitum,FRS181603220\\n+FOzpU07DHeuG0xvJKwZ/c4mg6UC03UYr7hzuw8wlxI=,What\\'s Hardcore,CASC80500003\\nYEmZkoXmuVYyM+MWVERJffiTY8rPqBjjEq8cr4pW71U=,Prelude,DEN960001271\\nDUeVUQpipsQkNqC91KbSZhCTFa179FWRtMC13bo2DTs=,Black Pearl,DEA311101514\\nt+OrpPUik9RXAn0SYuGA1IgT4anavkQ4do83gwol0O8=,We Believe,USBR21200163\\n2BZgHyaPBw0eEa5JhrNf1lVBNg5a00xLHqWjcgBGqE0=,Glass Walls,TCACP1616755\\nDIUxLMZ02iCIma3yWAD/XA3gv8NkPRELstr0kb8T1qk=,It Don\\'t Mean a Thing (If It Ain\\'t Got That Swing),USB4U0650584\\n6rV47AnCgwaZvPQ99P7E//l42yPx9AKswl6ltBhqBPQ=,Bizarre,US1A10111204\\nknv7fDAm0SSL01Hzrsjwk8ry+8wyD8E8zyJAbBnIHMM=,เผื่อใจไม่เป็น,THG010732285\\nsIGoywZE1eqPuvf30khYUwM3+bem4QA2JoY7QJKln7E=,Miami Ultra,DEVR31327883\\nbufOLt6WrKsiJJcMXWpWq+jem7JEKGsQF8zaL1F08Es=,La Bomba,UKACT1675762\\n+SowZdDcMUJ9V0WL6GnqSfysDOsc7TS7LPkvUWNiM3Q=,Roof On Fire (ROOF ON FIRE),JPZ920500914\\nQBLLTm9SRf/5fecbbpUVOcbNcSetiTNpSdcl9OdxpZQ=,Jerusalem,AULH00800039\\n597WdkZXmNyqGA8wzL9rsaluRdIcIsCDWU2INeXSFPA=,Das Kinderspiel in A Major| K. 598,PLL431661599\\nUcAIPpbrWgFLCO2cctvyOOhZ2Ro2Bl/kqlJAPSNY0Co=,Wedding Song (Song for Louis),USV351444550\\nCvK3NVl0hkcVvRUB0J1JQJDVOdWmE8iIxqEvfwshdXI=,I Got Money Now,USLF20600034\\n2Ws4dHSLdcEOlW+BERJro9zsZNxA48KJfCxGhWhivb4=,Can\\'t feel my Face (Remix),AUV401434283\\nGYcCnH2bHiOVayWtFz/27RCPdVCJDsiChDcsXeB8h38=,The Lawnmower,USA2P1446494\\nkkJRrCkBB7a7SfZiAeutFszwaPmF7cv1FqEurAMSSi8=,Par la porte entr\\'ouverte,DEBL60341492\\nanEkhKE1SJG383MO1ZKbTI7mXtHzbcrLMn8P64bccUE=,Low Down,USA371499095\\n3H2MrW2HQYEpb4Tg8pKd6nr/F1E9fk17gPZAjyHIVYE=,Lolly,USCBK1311490\\nEQs6SxD+fP/kvJsu0/DWdG8OVFTlgqFJKnKwGolOLk4=,Willie the Wimp (And His Cadillac Coffin),USSM18600061\\ntamEb9Nd7aMY+9GI8aO+lDylMjAA8Z0OMSG8DD88sZ4=,San Fransisco Scene,QMDA71425867\\nZDmc7uNpLXNGvgeCuScouCsyi4w24yxx7sMBSk/CRQI=,Soulful Music,GBQKL0500025\\nPG6vL5GjaD3iJh3vMY3IoyrToIM5jArjf0N3AVLDGqk=,Fortissimo,DEH740603429\\nbIMXoT5gNOhGof0iq7f83zdo3c9OvnLraLjehiPjkuA=,Wesh Alors (Feat.Seth Gueko),FR6V80802425\\ncZS3llS4KKuyS0IO6wDLpL/O3ABqg8vlh7imYwpg7Ds=,On Point,US4E41631205\\nFGP70eNn34n2EnbCIYXdJy1LdD6hgAI+aFeSpBL3F/o=,Cover All Sides,US3R41326603\\nPTMNbMixgpfQRKtdLbqXbwOAGdDxOzf7Jd7REMkLT3M=,From A Distance,USRDG0510129\\nqewrGC5tC/Musk35YTs2fkl7uizzo/v1zbJTcdB+2ak=,Runnin\\' Out Of Fools,DEPZ68981753\\ntOBBLF9y+5lILv3wFW7UZxYBxgz9xfVrVa6rJ+GUN8E=,Let Go,DEAF75103674\\n3ODJERfdPBNjHujSP6Br4ycHDUAFNTEWIi3gxQLr+UM=,Your Life,UKACT1661629\\nNxOTW5rVB0PdKmGXU6OdvEtRd6rFtmHMEH8mdOF1rK8=,Ohh La La!,USA2P1686565\\nwU09BwSbC672bhupoK2TkspwZ9M/AI3M8ZwZ2k2Oldw=,Polonaise No.3 in A| Op.40 No.1 - Military\"']\n"
     ]
    }
   ],
   "source": [
    "\n",
    "print(df_songs_extra_dataset[df_songs_extra_dataset['song_id']=='IBbV7L+0kPnbIUX8L6ltsmUmiSTp3fm3yD8yRt078FQ=']['name'].values)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**这条记录，双引号只有开，没有闭，在csv文件中，双引号必须要成对出现，因为一个双引号里面的值只能代表一个值，所以只要没有遇到下一个结束的双引号，那么这些记录都只算是一个值。**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据校正 -- 对有格式错误的数据进行校正"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 寻找列数不对和缺少双引号的数据，然后手动改\n",
    "def find_error_data(dataset_file, column_length):\n",
    "    with open(dataset_file, 'r+') as r:\n",
    "        while True:\n",
    "            line = r.readline()     # 逐行读取\n",
    "            if not line:\n",
    "                print('--end--')\n",
    "                break\n",
    "            columns = line.split(',')\n",
    "            if(len(columns) != column_length):\n",
    "                print('列数不对: ',line)\n",
    "            else:\n",
    "                line_new = []\n",
    "                for s in columns:\n",
    "                    if (s.count('\"') % 2 != 0):  \n",
    "                        print('缺少双引号: ', line)\n",
    "                        "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--end--\n"
     ]
    }
   ],
   "source": [
    "find_error_data(dpath +'songs.csv', 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--end--\n"
     ]
    }
   ],
   "source": [
    "find_error_data(dpath +'members.csv', 7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 128,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--end--\n"
     ]
    }
   ],
   "source": [
    "find_error_data(dpath +'test.csv', 6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--end--\n"
     ]
    }
   ],
   "source": [
    "find_error_data(dpath +'train.csv', 6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**对所有csv文件手动校正之后再进行特征工程**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 用户数据（members.csv）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>registration_init_time</th>\n",
       "      <th>expiration_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>XQxgAYj3klVKjR3oxPPXYYFp4soD4TuBghkhMTD4oTw=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>20110820</td>\n",
       "      <td>20170920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>UizsfmJb9mV54qE9hCYyU07Va97c0lCRLEQX3ae+ztM=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7</td>\n",
       "      <td>20150628</td>\n",
       "      <td>20170622</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>D8nEhsIOBSoE6VthTaqDX8U6lqjJ7dLdr72mOyLya2A=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>20160411</td>\n",
       "      <td>20170712</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>mCuD+tZ1hERA/o5GPqk38e041J8ZsBaLcu7nGoIIvhI=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>20150906</td>\n",
       "      <td>20150907</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>q4HRBfVSssAFS9iRfxWrohxuk9kCYMKjHOEagUMV6rQ=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>20170126</td>\n",
       "      <td>20170613</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                           msno  city  bd gender  \\\n",
       "0  XQxgAYj3klVKjR3oxPPXYYFp4soD4TuBghkhMTD4oTw=     1   0    NaN   \n",
       "1  UizsfmJb9mV54qE9hCYyU07Va97c0lCRLEQX3ae+ztM=     1   0    NaN   \n",
       "2  D8nEhsIOBSoE6VthTaqDX8U6lqjJ7dLdr72mOyLya2A=     1   0    NaN   \n",
       "3  mCuD+tZ1hERA/o5GPqk38e041J8ZsBaLcu7nGoIIvhI=     1   0    NaN   \n",
       "4  q4HRBfVSssAFS9iRfxWrohxuk9kCYMKjHOEagUMV6rQ=     1   0    NaN   \n",
       "\n",
       "   registered_via  registration_init_time  expiration_date  \n",
       "0               7                20110820         20170920  \n",
       "1               7                20150628         20170622  \n",
       "2               4                20160411         20170712  \n",
       "3               9                20150906         20150907  \n",
       "4               4                20170126         20170613  "
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "(34403, 7)"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_members = pd.read_csv(dpath +'members_copy.csv')\n",
    "df_members.head()\n",
    "df_members.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 34403 entries, 0 to 34402\n",
      "Data columns (total 7 columns):\n",
      "msno                      34403 non-null object\n",
      "city                      34403 non-null int64\n",
      "bd                        34403 non-null int64\n",
      "gender                    14501 non-null object\n",
      "registered_via            34403 non-null int64\n",
      "registration_init_time    34403 non-null int64\n",
      "expiration_date           34403 non-null int64\n",
      "dtypes: int64(5), object(2)\n",
      "memory usage: 1.8+ MB\n"
     ]
    }
   ],
   "source": [
    "df_members.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 检查到期时间和注册时间的大小是否正确"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>msno</th>\n",
       "      <th>city</th>\n",
       "      <th>bd</th>\n",
       "      <th>gender</th>\n",
       "      <th>registered_via</th>\n",
       "      <th>registration_init_time</th>\n",
       "      <th>expiration_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16867</th>\n",
       "      <td>1Y+bNz3FxSoJnKOcR/Q8VJGXZbWIstrW0HfBe5LZzKA=</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>20140501</td>\n",
       "      <td>19700101</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                               msno  city  bd gender  \\\n",
       "16867  1Y+bNz3FxSoJnKOcR/Q8VJGXZbWIstrW0HfBe5LZzKA=     1   0    NaN   \n",
       "\n",
       "       registered_via  registration_init_time  expiration_date  \n",
       "16867               9                20140501         19700101  "
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_members[df_members['expiration_date'] < df_members['registration_init_time']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**这条记录有问题，到期时间 > 注册时间，所以把这条记录的到期时间改为注册时间后，再做特征工程**"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
